package com.css.zfzx.sjcj.modules.drill.service.impl;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import com.alibaba.fastjson.JSONObject;
import com.css.bpm.platform.base.response.RestResponse;
import com.css.zfzx.sjcj.common.utils.CheckObjFields;
import com.css.zfzx.sjcj.modules.drill.Entity.*;
import com.css.zfzx.sjcj.modules.drill.Entity.vo.*;
import com.css.zfzx.sjcj.modules.drill.base.baseAttachment.service.BaseAttachmentService;
import com.css.zfzx.sjcj.modules.drill.base.basedivision.service.BaseDivisionService;
import com.css.zfzx.sjcj.modules.drill.repository.QhY02Repository;
import com.css.zfzx.sjcj.modules.drill.service.*;
import com.css.zfzx.sjcj.modules.drill.viewobjects.QhY02QueryParams;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.beanutils.ConvertUtils;
import org.apache.commons.beanutils.converters.DateConverter;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletInputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.servlet.http.Part;
import java.io.*;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import java.util.zip.ZipEntry;
import java.util.zip.ZipFile;
import java.util.zip.ZipInputStream;
import java.util.zip.ZipOutputStream;

/**
 * @Description
 * @Author zyc
 * @Date 2021/1/15 17:07
 * @Version V1.0
 */
@Service
@Slf4j
@PropertySource("classpath:platform-config.yml")
public class MutipleSheetExcelPoiServiceImpl implements MutipleSheetExcelPoiService {

    @Autowired
    QhY02Service qhY02Service;
    @Autowired
    QhY04Service qhY04Service;
    @Autowired
    QhY05Service qhY05Service;
    @Autowired
    QhY06Service qhY06Service;
    @Autowired
    QhY08Service qhY08Service;
    @Autowired
    QhY18Service qhY18Service;
    @Autowired
    BaseDivisionService baseDivisionService;
    @Autowired
    BaseAttachmentService baseAttachmentService;
    @Autowired
    QhY02Repository qhY02Repository;

    @Value("${file-upload.attachment-directory.config.defaultDirectory.fileDirectory}")
    String filePath;
    /**
     * 导入多sheet Excel文件
     */
    @Override
    @Transactional
    public RestResponse importMultiSheetExcel(MultipartFile excelFile) {
        log.info("文件名:[{}]", excelFile.getOriginalFilename());
        List<String> allCode = qhY02Repository.getAllCode();
        String message = "导入成功";
        try {
            Workbook workBook = WorkbookFactory.create(excelFile.getInputStream());
            StringBuilder sb = new StringBuilder();
            ImportParams param = new ImportParams();
            String drillId = null;
            for (int numSheet = 0; numSheet < workBook.getNumberOfSheets(); numSheet++) {
                param.setTitleRows(0);
                param.setHeadRows(1);
                param.setStartSheetIndex(numSheet);
                if (numSheet == 0) {
                    Sheet sheet = workBook.getSheet("Y02成孔信息");
                    QhY02Entity qhY02Entity = new QhY02Entity();
                    Cell cell0_1 = sheet.getRow(0).getCell(1);
                    cell0_1.setCellType(CellType.STRING);
                    if (null==cell0_1 || allCode.contains(cell0_1.getStringCellValue())) {
                        message = "导入失败！ 钻孔综合编号:" + cell0_1.getStringCellValue() + "重复！";
                        return RestResponse.fail(message);
                    }
                    qhY02Entity.setDrillSynthesizeCode(cell0_1.getStringCellValue());
                    qhY02Entity.setDrillDependProject(sheet.getRow(1).getCell(1).getStringCellValue());
                    qhY02Entity.setProjectCompleteInstitution(sheet.getRow(2).getCell(1).getStringCellValue());
                    //SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月");
                    //导入日期格式
                    Cell cell3_1 = sheet.getRow(3).getCell(1);
                    qhY02Entity.setProjectCompleteDate(importByExcelForDate(cell3_1));

                    qhY02Entity.setProjectPrincipal(sheet.getRow(4).getCell(1).getStringCellValue());
                    qhY02Entity.setDrillConstructionInstitution(sheet.getRow(5).getCell(1).getStringCellValue());
                    qhY02Entity.setDrillConstructionPrincipal(sheet.getRow(6).getCell(1).getStringCellValue());
                    Cell cell7_1 = sheet.getRow(7).getCell(1);
                    qhY02Entity.setConstructionStartDate(importByExcelForDate(cell7_1));
                    qhY02Entity.setConstructionReportDate(importByExcelForDate(sheet.getRow(8).getCell(1)));
                    qhY02Entity.setDrillCode(importByExcelForDate(sheet.getRow(9).getCell(1)));
                    String drillX = importByExcelForDate(sheet.getRow(10).getCell(1));
                    if (StringUtils.isNotEmpty(drillX)) {
                        qhY02Entity.setDrillX(Double.valueOf(drillX));
                    }
                    String drillY = importByExcelForDate(sheet.getRow(11).getCell(1));
                    if (StringUtils.isNotEmpty(drillY)) {
                        qhY02Entity.setDrillY(Double.valueOf(drillY));
                    }
                    qhY02Entity.setLongitude(importByExcelForDate(sheet.getRow(12).getCell(1)));
                    qhY02Entity.setLatitude(importByExcelForDate(sheet.getRow(13).getCell(1)));
                    String drillDepth = importByExcelForDate(sheet.getRow(14).getCell(1));
                    if (StringUtils.isNotEmpty(drillDepth)) {
                        qhY02Entity.setDrillDepth(Double.valueOf(drillDepth));
                    }
                    String elevation = importByExcelForDate(sheet.getRow(15).getCell(1));
                    if (StringUtils.isNotEmpty(elevation)) {
                        qhY02Entity.setElevation(Double.valueOf(elevation));
                    }
                    String stableWaterDepth = importByExcelForDate(sheet.getRow(16).getCell(1));
                    if (StringUtils.isNotEmpty(stableWaterDepth)) {
                        qhY02Entity.setStableWaterDepth(Double.valueOf(stableWaterDepth));
                    }
                    qhY02Entity.setCoordinatesDeclare(importByExcelForDate(sheet.getRow(17).getCell(1)));
                    qhY02Entity.setElevationDeclare(importByExcelForDate(sheet.getRow(18).getCell(1)));
                    qhY02Entity.setProvince(importByExcelForDate(sheet.getRow(19).getCell(1)));
                    qhY02Entity.setCity(importByExcelForDate(sheet.getRow(20).getCell(1)));
                    qhY02Entity.setArea(importByExcelForDate(sheet.getRow(21).getCell(1)));
                    qhY02Entity.setVillage(importByExcelForDate(sheet.getRow(22).getCell(1)));
                    qhY02Entity.setDrillRemark(importByExcelForDate(sheet.getRow(23).getCell(1)));
                    //QhY02Entity qhY02Entity1 = qhY02Service.saveQhY02(qhY02Entity);
//                    if(StringUtils.isEmpty(qhY02Entity.getDrillDependProject())){
//                        message = "导入失败！ 钻孔依托的项目不能为空！";
//                        return RestResponse.fail(message);
//                    }
//                    if(StringUtils.isEmpty(qhY02Entity.getProjectCompleteInstitution())){
//                        message = "导入失败！ 项目完成单位不能为空！";
//                        return RestResponse.fail(message);
//                    }
//                    if(StringUtils.isEmpty(qhY02Entity.getProjectCompleteDate())){
//                        message = "导入失败！ 项目完成时间不能为空！";
//                        return RestResponse.fail(message);
//                    }
//                    if(StringUtils.isEmpty(qhY02Entity.getProjectPrincipal())){
//                        message = "导入失败！ 项目负责人不能为空！";
//                        return RestResponse.fail(message);
//                    }
//                    if(StringUtils.isEmpty(qhY02Entity.getDrillConstructionInstitution())){
//                            message = "导入失败！ 钻孔勘察单位不能为空！";
//                        return RestResponse.fail(message);
//                    }
//                    if(StringUtils.isEmpty(qhY02Entity.getDrillConstructionPrincipal())){
//                        message = "导入失败！ 钻孔勘察负责人不能为空！";
//                        return RestResponse.fail(message);
//                    }
//                    if(StringUtils.isEmpty(qhY02Entity.getConstructionStartDate())){
//                        message = "导入失败！ 勘察施工日期不能为空！";
//                        return RestResponse.fail(message);
//                    }
//                    if(StringUtils.isEmpty(qhY02Entity.getConstructionReportDate())){
//                        message = "导入失败！ 勘察报告日期不能为空！";
//                        return RestResponse.fail(message);
//                    }
//                    if(StringUtils.isEmpty(qhY02Entity.getDrillCode())){
//                        message = "导入失败！ 钻孔编号不能为空！";
//                        return RestResponse.fail(message);
//                    }
//                    if(StringUtils.isEmpty(qhY02Entity.getDrillCode())){
//                        message = "导入失败！ 钻孔编号不能为空！";
//                        return RestResponse.fail(message);
//                    }
                    JSONObject jsonObject = qhY02Service.saveQhY02(qhY02Entity);
                    QhY02Entity qhY02Entity1 = (QhY02Entity) jsonObject.get("data");
                    drillId = qhY02Entity1.getId();
                } else if (numSheet == 1) {
                    List<QhY04Entity> qhY04Entities = ExcelImportUtil.importExcel(excelFile.getInputStream(), QhY04Entity.class, param);
                    for (QhY04Entity qhY04Entity : qhY04Entities) {
                        if (drillId != null && (!CheckObjFields.isAllFieldNull(qhY04Entity))) {
                            qhY04Entity.setDrillId(drillId);
                            qhY04Service.saveQhY04(qhY04Entity);
                        }
                    }
                } else if (numSheet == 2) {
                    ExcelImportResult<QhY05Entity> result = ExcelImportUtil.importExcelMore(excelFile.getInputStream(),
                            QhY05Entity.class, param);
                    List<QhY05Entity> qhY05Entities = result.getList();
                    for (QhY05Entity qhY05Entity : qhY05Entities) {
                        if (drillId != null && (!CheckObjFields.isAllFieldNull(qhY05Entity))) {
                            qhY05Entity.setDrillId(drillId);
                            qhY05Service.saveQhY05(qhY05Entity);
                        }
                    }
                } else if (numSheet == 3) {
                    ExcelImportResult<QhY06Entity> result = ExcelImportUtil.importExcelMore(excelFile.getInputStream(),
                            QhY06Entity.class, param);
                    List<QhY06Entity> qhY06Entities = result.getList();
                    for (QhY06Entity qhY06Entity : qhY06Entities) {
                        if (drillId != null && (!CheckObjFields.isAllFieldNull(qhY06Entity))) {
                            qhY06Entity.setDrillId(drillId);
                            qhY06Service.saveQhY06(qhY06Entity);
                        }
                    }
                } else if (numSheet == 4) {
                    ExcelImportResult<QhY08Entity> result = ExcelImportUtil.importExcelMore(excelFile.getInputStream(),
                            QhY08Entity.class, param);
                    List<QhY08Entity> qhY08Entities = result.getList();
                    for (QhY08Entity qhY08Entity : qhY08Entities) {
                        if (drillId != null && (!CheckObjFields.isAllFieldNull(qhY08Entity))) {
                            qhY08Entity.setDrillId(drillId);
                            qhY08Service.saveQhY08(qhY08Entity);
                        }
                    }
                } else if (numSheet == 5) {
                    QhY18Entity qhY18Entity = new QhY18Entity();
                    Sheet sheet = workBook.getSheet("Y18场地类别");
                    Cell cell0_1 = sheet.getRow(0).getCell(1);
                    if (cell0_1 != null) {
                        qhY18Entity.setSiteType(cell0_1.getStringCellValue());
                        qhY18Entity.setDrillId(drillId);
                        qhY18Service.saveQhY18(qhY18Entity);
                    }
                }
            }
        } catch (
                Exception e) {
            e.printStackTrace();
            log.error("导入失败");
            return RestResponse.fail("导入失败,请检查输入的数据格式");
        }
        return RestResponse.succeed(message);
    }

    //检验是否为日期格式
    public static String importByExcelForDate(Cell currentCell) {
        String cellValue = "";
        if (currentCell == null) {
            return null;
        }
        switch (currentCell.getCellType()) {
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(currentCell)) {
                    double d = currentCell.getNumericCellValue();
                    Date date = DateUtil.getJavaDate(d);
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    cellValue = sdf.format(date);
                    System.out.println("=========date=" + sdf.format(date));
                } else {
                    NumberFormat nf = NumberFormat.getInstance();
                    nf.setGroupingUsed(false);//true时的格式：1,234,567,890
                    nf.setMaximumFractionDigits(8);
                    cellValue = nf.format(currentCell.getNumericCellValue());//数值类型的数据为double，所以需要转换一下
                    System.out.println("===CELL_TYPE_NUMERIC" + cellValue);
                }
                break;
            case STRING:
                System.out.println("===CELL_TYPE_STRING" + currentCell.getStringCellValue());
                cellValue = currentCell.getStringCellValue();
                break;
            case BOOLEAN:
                System.out.println("====CELL_TYPE_BOOLEAN" + currentCell.getBooleanCellValue());
                cellValue = String.valueOf(currentCell.getBooleanCellValue());
                break;
            case FORMULA:
                System.out.println("====CELL_TYPE_FORMULA" + currentCell.getCellFormula());
                cellValue = String.valueOf(currentCell.getCellFormula());
                break;
            default:
                cellValue = "";
                System.out.println("unsuported cell type");
                break;
        }
        return cellValue;
    }


    /**
     * 批量excel多sheet导出
     *
     * @param response
     * @param ids
     * @return
     */
    @Override
    public RestResponse multipleSheetZipExports(HttpServletResponse response, List<String> ids) {
        try {
            String dateStr = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
            String zipName = dateStr + "区划采集Excel压缩包导出.zip";
            response.setContentType("application/octet-stream");
            response.setHeader("Connection", "close"); // 表示不能用浏览器直接打开
            response.setHeader("Accept-Ranges", "bytes");// 告诉客户端允许断点续传多线程连接下载
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + new String(zipName.getBytes("GB2312"), "ISO8859-1"));
            response.setCharacterEncoding("UTF-8");
            OutputStream out = response.getOutputStream();
            ZipOutputStream zos = new ZipOutputStream(out);

            for (String id : ids) {
                HSSFWorkbook workbook = new HSSFWorkbook();// 创建一个Excel文件
                HSSFDataFormat dataFormat = workbook.createDataFormat(); //设置格式
                // 样式设置
                HSSFCellStyle columnHeadStyle = workbook.createCellStyle();
                columnHeadStyle.setFillPattern(FillPatternType.NO_FILL); // 背景
                columnHeadStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); // 背景色AQUA.getIndex()
                columnHeadStyle.setBorderBottom(BorderStyle.THIN); // 下边框
                columnHeadStyle.setBorderLeft(BorderStyle.THIN);// 左边框
                columnHeadStyle.setBorderTop(BorderStyle.THIN);// 上边框
                columnHeadStyle.setBorderRight(BorderStyle.THIN);// 右边框
                columnHeadStyle.setAlignment(HorizontalAlignment.LEFT);//水平居中
                columnHeadStyle.setWrapText(true);//自动换行
                // 全局样式设置
                HSSFCellStyle allStyle = workbook.createCellStyle();
                columnHeadStyle.setFillPattern(FillPatternType.NO_FILL); // 背景
                allStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());// 填充白色
                allStyle.setBorderBottom(BorderStyle.THIN); // 下边框
                allStyle.setBorderLeft(BorderStyle.THIN);// 左边框
                allStyle.setBorderTop(BorderStyle.THIN);// 上边框
                allStyle.setBorderRight(BorderStyle.THIN);// 右边框
                allStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
                allStyle.setWrapText(true);//自动换行
                //allStyle.setDataFormat(dataFormat.getFormat("@")); //设置日期格式

                //处理日期格式
                HSSFCellStyle dateStyle = workbook.createCellStyle();
                dateStyle.setBorderBottom(BorderStyle.THIN); // 下边框
                dateStyle.setBorderLeft(BorderStyle.THIN);// 左边框
                dateStyle.setBorderTop(BorderStyle.THIN);// 上边框
                dateStyle.setBorderRight(BorderStyle.THIN);// 右边框
                dateStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
                dateStyle.setWrapText(true);//自动换行
                dateStyle.setDataFormat(dataFormat.getFormat("yyyy-MM-dd")); //设置日期格式

                //处理数字格式
                HSSFCellStyle numberStyle = workbook.createCellStyle();
                numberStyle.setBorderBottom(BorderStyle.THIN); // 下边框
                numberStyle.setBorderLeft(BorderStyle.THIN);// 左边框
                numberStyle.setBorderTop(BorderStyle.THIN);// 上边框
                numberStyle.setBorderRight(BorderStyle.THIN);// 右边框
                numberStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
                numberStyle.setWrapText(true);//自动换行
                numberStyle.setDataFormat(dataFormat.getFormat("0.0000")); //设置日期格式

                // 创建Y02成孔信息Sheet
                QhY02Entity qhY02Entity = qhY02Service.getQhY02(id);
                QhY02Vo qhY02Vo = new QhY02Vo();
                HSSFSheet sheet = workbook.createSheet("Y02成孔信息");
                String[] sheetHeadTitle = {"钻孔综合编号", "钻孔依托的项目", "项目完成单位", "项目完成时间", "项目负责人", "钻孔勘察单位",
                        "钻孔勘察负责人", "勘察施工日期", "勘察报告日期", "钻孔编号", "孔口记录坐标X轴", "孔口记录坐标Y轴", "孔位经度（°）", "孔位纬度（°）",
                        "钻孔深度（m）", "地面高程（m）", "稳定水位深度（m）", "坐标系说明", "高程系说明", "钻孔所在省级行政区", "钻孔所在地市级行政区",
                        "钻孔所在县级行政区", "钻孔所在乡级行政区", "钻孔说明备注"};

                ConvertUtils.register(new DateConverter(null), java.util.Date.class);
                BeanUtils.copyProperties(qhY02Vo, qhY02Entity);
                String[] strings = PoiExcelUtil.objectChangeArray(qhY02Vo);
                for (int i = 0; i < sheetHeadTitle.length; i++) {
                    HSSFRow row0 = sheet.createRow(i);
                    row0.setHeightInPoints(30);
                    HSSFCell cel0_1 = row0.createCell(0);
                    cel0_1.setCellValue(new HSSFRichTextString(sheetHeadTitle[i]));
                    cel0_1.setCellStyle(columnHeadStyle);

                    HSSFCell cel0_2 = row0.createCell(1);
                    cel0_2.setCellValue(strings[i]);
                    if (i == 3 || i == 7 || i == 8) {
                        cel0_2.setCellStyle(dateStyle);
                    } else {
                        cel0_2.setCellStyle(allStyle);
                    }
                    sheet.autoSizeColumn((short) i);
                }

                //Y04sheet
                HSSFSheet y04Sheet = workbook.createSheet("Y04地层岩性分层");
                List<QhY04Entity> qhY04EntityList = qhY04Service.findByDrillId(id);
                String[] y04SheetHeadTitle = {"地层序号", "地层编号", "地层年代", "时代成因", "层底深度（m）", "分层厚度（m）", "岩土名称", "岩土特征"};
                for (int k = 0; k <= qhY04EntityList.size(); k++) {
                    HSSFRow rowk = y04Sheet.createRow(k);
                    rowk.setHeightInPoints(30);
                    for (int i = 0; i < y04SheetHeadTitle.length; i++) {
                        if (k == 0) {
                            HSSFCell cel1_i = rowk.createCell(i);
                            cel1_i.setCellValue(new HSSFRichTextString(y04SheetHeadTitle[i]));
                            cel1_i.setCellStyle(columnHeadStyle);
                        } else {
                            QhY04Vo qhY04Vo = new QhY04Vo();
                            SpringUtil.copyPropertiesIgnoreNull(qhY04EntityList.get(k - 1), qhY04Vo);
                            String[] y04Values = PoiExcelUtil.objectChangeArray(qhY04Vo);

                            HSSFCell celk_i = rowk.createCell(i);
                            celk_i.setCellValue(new HSSFRichTextString(y04Values[i]));
                            celk_i.setCellStyle(allStyle);
                        }
                        sheet.autoSizeColumn(i);
                    }
                }

                //Y05sheet
                HSSFSheet Y05sheet = workbook.createSheet("Y05标贯值");
                List<QhY05Entity> qhY05EntityList = qhY05Service.findByDrillId(id);
                String[] Y05sheetHeadTitle = {"起始标贯深度(m)", "结束标贯深度(m)", "标贯值"};
                for (int k = 0; k <= qhY05EntityList.size(); k++) {
                    HSSFRow rowk = Y05sheet.createRow(k);
                    rowk.setHeightInPoints(30);
                    for (int i = 0; i < Y05sheetHeadTitle.length; i++) {
                        if (k == 0) {
                            HSSFCell cel1_i = rowk.createCell(i);
                            cel1_i.setCellValue(new HSSFRichTextString(Y05sheetHeadTitle[i]));
                            cel1_i.setCellStyle(allStyle);
                        } else {
                            QhY05Vo qhY05Vo = new QhY05Vo();
                            SpringUtil.copyPropertiesIgnoreNull(qhY05EntityList.get(k - 1), qhY05Vo);
                            String[] y05Values = PoiExcelUtil.objectChangeArray(qhY05Vo);

                            HSSFCell celk_i = rowk.createCell(i);
                            celk_i.setCellValue(new HSSFRichTextString(y05Values[i]));
                            celk_i.setCellStyle(allStyle);
                        }
                    }
                    sheet.autoSizeColumn((short) k);
                }

                //Y06sheet
                HSSFSheet Y06sheet = workbook.createSheet("Y06剪切波实测值");
                List<QhY06Entity> qhY06EntityList = qhY06Service.findByDrillId(id);
                String[] Y06sheetHeadTitle = {"序号", "岩土性名称", "层底深度 (m)", "层厚(m)", "剪切波波速V(m/s)"};
                for (int k = 0; k <= qhY06EntityList.size(); k++) {
                    HSSFRow rowk = Y06sheet.createRow(k);
                    rowk.setHeightInPoints(30);
                    for (int i = 0; i < Y06sheetHeadTitle.length; i++) {
                        if (k == 0) {
                            HSSFCell cel1_i = rowk.createCell(i);
                            cel1_i.setCellValue(new HSSFRichTextString(Y06sheetHeadTitle[i]));
                            cel1_i.setCellStyle(allStyle);
                        } else {
                            QhY06Vo qhY06Vo = new QhY06Vo();
                            SpringUtil.copyPropertiesIgnoreNull(qhY06EntityList.get(k - 1), qhY06Vo);
                            String[] y06Values = PoiExcelUtil.objectChangeArray(qhY06Vo);

                            HSSFCell celk_i = rowk.createCell(i);
                            celk_i.setCellValue(new HSSFRichTextString(y06Values[i]));
                            celk_i.setCellStyle(allStyle);
                        }
                    }
                    sheet.autoSizeColumn((short) k);
                }

                //Y08sheet
                HSSFSheet Y08sheet = workbook.createSheet("Y08土动力试验参数");
                List<QhY08Entity> qhY08EntityList = qhY08Service.findByDrillId(id);
                String[] Y08sheetHeadTitle = {"序号", "样品编号", "取样深度顶点(m)", "取样深度底点(m)", "土层名称", "密度(kg/m3)", "模量比(G/Gmax)剪应变(γ)（10-4×0.05)", "模量比(G/Gmax)剪应变(γ)（10-4×0.10)",
                        "模量比(G/Gmax)剪应变(γ)（10-4×0.50)", "模量比(G/Gmax)剪应变(γ)（10-4×1.00)", "模量比(G/Gmax)剪应变(γ)（10-4×5.00)", "模量比(G/Gmax)剪应变(γ)（10-4×10.00)", "模量比(G/Gmax)剪应变(γ)（10-4×50.00)",
                        "模量比(G/Gmax)剪应变(γ)（10-4×100.00)", "阻尼比(λ)剪应变(γ)（10-4×0.05)", "阻尼比(λ)剪应变(γ)（10-4×0.10)", "阻尼比(λ)剪应变(γ)（10-4×0.50)", "阻尼比(λ)剪应变(γ)（10-4×1.00)", "阻尼比(λ)剪应变(γ)（10-4×5.00)", "阻尼比(λ)剪应变(γ)（10-4×10.00)",
                        "阻尼比(λ)剪应变(γ)（10-4×50.00)", "阻尼比(λ)剪应变(γ)（10-4×100.00)"};
                for (int k = 0; k <= qhY08EntityList.size(); k++) {
                    HSSFRow rowk = Y08sheet.createRow(k);
                    for (int i = 0; i < Y08sheetHeadTitle.length; i++) {
                        if (k == 0) {
                            rowk.setHeightInPoints(60);
                            HSSFCell cel1_i = rowk.createCell(i);
                            cel1_i.setCellValue(new HSSFRichTextString(Y08sheetHeadTitle[i]));
                            cel1_i.setCellStyle(allStyle);
                        } else {
                            rowk.setHeightInPoints(30);
                            QhY08Vo qhY08Vo = new QhY08Vo();
                            SpringUtil.copyPropertiesIgnoreNull(qhY08EntityList.get(k - 1), qhY08Vo);
                            String[] y08Values = PoiExcelUtil.objectChangeArray(qhY08Vo);

                            HSSFCell celk_i = rowk.createCell(i);
                            celk_i.setCellValue(new HSSFRichTextString(y08Values[i]));
                            celk_i.setCellStyle(allStyle);
                        }
                        sheet.autoSizeColumn(i);
                    }

                }
                //Y18sheet
                HSSFSheet Y18sheet = workbook.createSheet("Y18场地类别");
                QhY18Entity qhY18Entity = qhY18Service.findDrillId(id);
                HSSFRow row0 = Y18sheet.createRow(0);
                row0.setHeightInPoints(30);
                HSSFCell cell0_0 = row0.createCell(0);
                cell0_0.setCellValue(new HSSFRichTextString("场地类别"));
                cell0_0.setCellStyle(columnHeadStyle);
                if (qhY18Entity != null) {
                    HSSFCell cell0_1 = row0.createCell(1);
                    cell0_1.setCellValue(qhY18Entity.getSiteType());
                    cell0_1.setCellStyle(allStyle);
                }


//                DivisionVO divisionVO = new DivisionVO();
//                divisionVO.setProvince(qhY02Entity.getProvince());
//                divisionVO.setCity(qhY02Entity.getCity());
//                divisionVO.setArea(qhY02Entity.getArea());
//                String code = baseDivisionService.findDivisionCodeByName(divisionVO, "3");
                String fileName = qhY02Entity.getDrillSynthesizeCode() + ".xls";
                ZipEntry entry = new ZipEntry(fileName);
                zos.putNextEntry(entry);
                ByteArrayOutputStream bos = new ByteArrayOutputStream();
                workbook.write(bos);
                bos.writeTo(zos);
                zos.closeEntry();
            }
            if (zos != null) {
                zos.flush();
                zos.close();
            }
            return RestResponse.succeed();
        } catch (Exception e) {
            e.printStackTrace();
            return RestResponse.fail("fail");
        }
    }

    @Override
    public void multipleSheetsZipExports(HttpServletResponse response, QhY02QueryParams qhY02QueryParams) {
        JSONObject jsonObject = qhY02Service.queryQhY02s(qhY02QueryParams);
        List<QhY02Entity> qhY02List = (List<QhY02Entity>) jsonObject.get("data");
        try {
            String dateStr = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
            String zipName = dateStr + "区划采集Excel压缩包导出.zip";
            response.setContentType("application/octet-stream");
            response.setHeader("Connection", "close"); // 表示不能用浏览器直接打开
            response.setHeader("Accept-Ranges", "bytes");// 告诉客户端允许断点续传多线程连接下载
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + new String(zipName.getBytes("GB2312"), "ISO8859-1"));
            response.setCharacterEncoding("UTF-8");
            OutputStream out = response.getOutputStream();
            ZipOutputStream zos = new ZipOutputStream(out);

            for (QhY02Entity qhY02Entity : qhY02List) {
                String id = qhY02Entity.getId();
                HSSFWorkbook workbook = new HSSFWorkbook();// 创建一个Excel文件
                HSSFDataFormat dataFormat = workbook.createDataFormat(); //设置格式
                // 样式设置
                HSSFCellStyle columnHeadStyle = workbook.createCellStyle();
                columnHeadStyle.setFillPattern(FillPatternType.NO_FILL); // 背景
                columnHeadStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); // 背景色AQUA.getIndex()
                columnHeadStyle.setBorderBottom(BorderStyle.THIN); // 下边框
                columnHeadStyle.setBorderLeft(BorderStyle.THIN);// 左边框
                columnHeadStyle.setBorderTop(BorderStyle.THIN);// 上边框
                columnHeadStyle.setBorderRight(BorderStyle.THIN);// 右边框
                columnHeadStyle.setAlignment(HorizontalAlignment.LEFT);//水平居中
                columnHeadStyle.setWrapText(true);//自动换行
                // 全局样式设置
                HSSFCellStyle allStyle = workbook.createCellStyle();
                columnHeadStyle.setFillPattern(FillPatternType.NO_FILL); // 背景
                allStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());// 填充白色
                allStyle.setBorderBottom(BorderStyle.THIN); // 下边框
                allStyle.setBorderLeft(BorderStyle.THIN);// 左边框
                allStyle.setBorderTop(BorderStyle.THIN);// 上边框
                allStyle.setBorderRight(BorderStyle.THIN);// 右边框
                allStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
                allStyle.setWrapText(true);//自动换行
                //allStyle.setDataFormat(dataFormat.getFormat("@")); //设置日期格式

                //处理日期格式
                HSSFCellStyle dateStyle = workbook.createCellStyle();
                dateStyle.setBorderBottom(BorderStyle.THIN); // 下边框
                dateStyle.setBorderLeft(BorderStyle.THIN);// 左边框
                dateStyle.setBorderTop(BorderStyle.THIN);// 上边框
                dateStyle.setBorderRight(BorderStyle.THIN);// 右边框
                dateStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
                dateStyle.setWrapText(true);//自动换行
                dateStyle.setDataFormat(dataFormat.getFormat("yyyy-MM-dd")); //设置日期格式

                //处理数字格式
                HSSFCellStyle numberStyle = workbook.createCellStyle();
                numberStyle.setBorderBottom(BorderStyle.THIN); // 下边框
                numberStyle.setBorderLeft(BorderStyle.THIN);// 左边框
                numberStyle.setBorderTop(BorderStyle.THIN);// 上边框
                numberStyle.setBorderRight(BorderStyle.THIN);// 右边框
                numberStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
                numberStyle.setWrapText(true);//自动换行
                numberStyle.setDataFormat(dataFormat.getFormat("0.0000")); //设置日期格式

                // 创建Y02成孔信息Sheet
                QhY02Vo qhY02Vo = new QhY02Vo();
                HSSFSheet sheet = workbook.createSheet("Y02成孔信息");
                String[] sheetHeadTitle = {"钻孔综合编号", "钻孔依托的项目", "项目完成单位", "项目完成时间", "项目负责人", "钻孔勘察单位",
                        "钻孔勘察负责人", "勘察施工日期", "勘察报告日期", "钻孔编号", "孔口记录坐标X轴", "孔口记录坐标Y轴", "孔位经度（°）", "孔位纬度（°）",
                        "钻孔深度（m）", "地面高程（m）", "稳定水位深度（m）", "坐标系说明", "高程系说明", "钻孔所在省级行政区", "钻孔所在地市级行政区",
                        "钻孔所在县级行政区", "钻孔所在乡级行政区", "钻孔说明备注"};

                ConvertUtils.register(new DateConverter(null), java.util.Date.class);
                BeanUtils.copyProperties(qhY02Vo, qhY02Entity);
                String[] strings = PoiExcelUtil.objectChangeArray(qhY02Vo);
                for (int i = 0; i < sheetHeadTitle.length; i++) {
                    HSSFRow row0 = sheet.createRow(i);
                    row0.setHeightInPoints(30);
                    HSSFCell cel0_1 = row0.createCell(0);
                    cel0_1.setCellValue(new HSSFRichTextString(sheetHeadTitle[i]));
                    cel0_1.setCellStyle(columnHeadStyle);

                    HSSFCell cel0_2 = row0.createCell(1);
                    cel0_2.setCellValue(strings[i]);
                    if (i == 3 || i == 7 || i == 8) {
                        cel0_2.setCellStyle(dateStyle);
                    } else {
                        cel0_2.setCellStyle(allStyle);
                    }
                    sheet.autoSizeColumn((short) i);
                }

                //Y04sheet
                HSSFSheet y04Sheet = workbook.createSheet("Y04地层岩性分层");
                List<QhY04Entity> qhY04EntityList = qhY04Service.findByDrillId(id);
                String[] y04SheetHeadTitle = {"地层序号", "地层编号", "地层年代", "时代成因", "层底深度（m）", "分层厚度（m）", "岩土名称", "岩土特征"};
                for (int k = 0; k <= qhY04EntityList.size(); k++) {
                    HSSFRow rowk = y04Sheet.createRow(k);
                    rowk.setHeightInPoints(30);
                    for (int i = 0; i < y04SheetHeadTitle.length; i++) {
                        if (k == 0) {
                            HSSFCell cel1_i = rowk.createCell(i);
                            cel1_i.setCellValue(new HSSFRichTextString(y04SheetHeadTitle[i]));
                            cel1_i.setCellStyle(columnHeadStyle);
                        } else {
                            QhY04Vo qhY04Vo = new QhY04Vo();
                            SpringUtil.copyPropertiesIgnoreNull(qhY04EntityList.get(k - 1), qhY04Vo);
                            String[] y04Values = PoiExcelUtil.objectChangeArray(qhY04Vo);

                            HSSFCell celk_i = rowk.createCell(i);
                            celk_i.setCellValue(new HSSFRichTextString(y04Values[i]));
                            celk_i.setCellStyle(allStyle);
                        }
                        sheet.autoSizeColumn(i);
                    }
                }

                //Y05sheet
                HSSFSheet Y05sheet = workbook.createSheet("Y05标贯值");
                List<QhY05Entity> qhY05EntityList = qhY05Service.findByDrillId(id);
                String[] Y05sheetHeadTitle = {"起始标贯深度(m)", "结束标贯深度(m)", "标贯值"};
                for (int k = 0; k <= qhY05EntityList.size(); k++) {
                    HSSFRow rowk = Y05sheet.createRow(k);
                    rowk.setHeightInPoints(30);
                    for (int i = 0; i < Y05sheetHeadTitle.length; i++) {
                        if (k == 0) {
                            HSSFCell cel1_i = rowk.createCell(i);
                            cel1_i.setCellValue(new HSSFRichTextString(Y05sheetHeadTitle[i]));
                            cel1_i.setCellStyle(allStyle);
                        } else {
                            QhY05Vo qhY05Vo = new QhY05Vo();
                            SpringUtil.copyPropertiesIgnoreNull(qhY05EntityList.get(k - 1), qhY05Vo);
                            String[] y05Values = PoiExcelUtil.objectChangeArray(qhY05Vo);

                            HSSFCell celk_i = rowk.createCell(i);
                            celk_i.setCellValue(new HSSFRichTextString(y05Values[i]));
                            celk_i.setCellStyle(allStyle);
                        }
                    }
                    sheet.autoSizeColumn((short) k);
                }

                //Y06sheet
                HSSFSheet Y06sheet = workbook.createSheet("Y06剪切波实测值");
                List<QhY06Entity> qhY06EntityList = qhY06Service.findByDrillId(id);
                String[] Y06sheetHeadTitle = {"序号", "岩土性名称", "层底深度 (m)", "层厚(m)", "剪切波波速V(m/s)"};
                for (int k = 0; k <= qhY06EntityList.size(); k++) {
                    HSSFRow rowk = Y06sheet.createRow(k);
                    rowk.setHeightInPoints(30);
                    for (int i = 0; i < Y06sheetHeadTitle.length; i++) {
                        if (k == 0) {
                            HSSFCell cel1_i = rowk.createCell(i);
                            cel1_i.setCellValue(new HSSFRichTextString(Y06sheetHeadTitle[i]));
                            cel1_i.setCellStyle(allStyle);
                        } else {
                            QhY06Vo qhY06Vo = new QhY06Vo();
                            SpringUtil.copyPropertiesIgnoreNull(qhY06EntityList.get(k - 1), qhY06Vo);
                            String[] y06Values = PoiExcelUtil.objectChangeArray(qhY06Vo);

                            HSSFCell celk_i = rowk.createCell(i);
                            celk_i.setCellValue(new HSSFRichTextString(y06Values[i]));
                            celk_i.setCellStyle(allStyle);
                        }
                    }
                    sheet.autoSizeColumn((short) k);
                }

                //Y08sheet
                HSSFSheet Y08sheet = workbook.createSheet("Y08土动力试验参数");
                List<QhY08Entity> qhY08EntityList = qhY08Service.findByDrillId(id);
                String[] Y08sheetHeadTitle = {"序号", "样品编号", "取样深度顶点(m)", "取样深度底点(m)", "土层名称", "密度(kg/m3)", "模量比(G/Gmax)剪应变(γ)（10-4×0.05)", "模量比(G/Gmax)剪应变(γ)（10-4×0.10)",
                        "模量比(G/Gmax)剪应变(γ)（10-4×0.50)", "模量比(G/Gmax)剪应变(γ)（10-4×1.00)", "模量比(G/Gmax)剪应变(γ)（10-4×5.00)", "模量比(G/Gmax)剪应变(γ)（10-4×10.00)", "模量比(G/Gmax)剪应变(γ)（10-4×50.00)",
                        "模量比(G/Gmax)剪应变(γ)（10-4×100.00)", "阻尼比(λ)剪应变(γ)（10-4×0.05)", "阻尼比(λ)剪应变(γ)（10-4×0.10)", "阻尼比(λ)剪应变(γ)（10-4×0.50)", "阻尼比(λ)剪应变(γ)（10-4×1.00)", "阻尼比(λ)剪应变(γ)（10-4×5.00)", "阻尼比(λ)剪应变(γ)（10-4×10.00)",
                        "阻尼比(λ)剪应变(γ)（10-4×50.00)", "阻尼比(λ)剪应变(γ)（10-4×100.00)"};
                for (int k = 0; k <= qhY08EntityList.size(); k++) {
                    HSSFRow rowk = Y08sheet.createRow(k);
                    for (int i = 0; i < Y08sheetHeadTitle.length; i++) {
                        if (k == 0) {
                            rowk.setHeightInPoints(60);
                            HSSFCell cel1_i = rowk.createCell(i);
                            cel1_i.setCellValue(new HSSFRichTextString(Y08sheetHeadTitle[i]));
                            cel1_i.setCellStyle(allStyle);
                        } else {
                            rowk.setHeightInPoints(30);
                            QhY08Vo qhY08Vo = new QhY08Vo();
                            SpringUtil.copyPropertiesIgnoreNull(qhY08EntityList.get(k - 1), qhY08Vo);
                            String[] y08Values = PoiExcelUtil.objectChangeArray(qhY08Vo);

                            HSSFCell celk_i = rowk.createCell(i);
                            celk_i.setCellValue(new HSSFRichTextString(y08Values[i]));
                            celk_i.setCellStyle(allStyle);
                        }
                        sheet.autoSizeColumn(i);
                    }

                }
                //Y18sheet
                HSSFSheet Y18sheet = workbook.createSheet("Y18场地类别");
                QhY18Entity qhY18Entity = qhY18Service.findDrillId(id);
                HSSFRow row0 = Y18sheet.createRow(0);
                row0.setHeightInPoints(30);
                HSSFCell cell0_0 = row0.createCell(0);
                cell0_0.setCellValue(new HSSFRichTextString("场地类别"));
                cell0_0.setCellStyle(columnHeadStyle);
                if (qhY18Entity != null) {
                    HSSFCell cell0_1 = row0.createCell(1);
                    cell0_1.setCellValue(qhY18Entity.getSiteType());
                    cell0_1.setCellStyle(allStyle);
                }

                String fileName = qhY02Entity.getDrillSynthesizeCode() + ".xls";
                zos.putNextEntry(new ZipEntry(fileName));
                ByteArrayOutputStream bos = new ByteArrayOutputStream();
                workbook.write(bos);

                // String accessory = qhY02Entity.getAccessory();
                // if (StringUtils.isEmpty(accessory) || "null".equals(accessory)) {
                //     continue;
                // }
                // FileInputStream fis=null;
                // String[] attachIds = accessory.split(",");
                // for (String attachId : attachIds) {
                //     String attachPath = baseAttachmentService.findByAttachId(attachId);
                //     File file = new File(filePath+attachPath);
                //     fis=new FileInputStream(file);
                //     byte[] bytes=new byte[1024];
                //     int i = fis.read(bytes);
                //     while (i!=-1){
                //         zos.write(bytes,0,i);
                //         i=fis.read(bytes);
                //     }
                // }
                bos.writeTo(zos);
            }
            zos.closeEntry();
            if (zos != null) {
                zos.flush();
                zos.close();
            }
            //return RestResponse.succeed();
        } catch (Exception e) {
            e.printStackTrace();
           // return RestResponse.fail("fail");
        }
    }

}
